package database.dbObjects;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

import main.Main;
import objects.Student;

public interface IDBStudent extends IDBContact {
	
	public class DBStudent extends DBContact {
		
		/*
		 * methods are final for optimization of the code (by the compiler)
		 * it is not possible anymore to inherit it after this declaration 
		 */
		
		public final void init() throws Exception {
			
			createTable();
			createTriggers();
		}
		
		private final void createTable() throws Exception {
			
			String query = "create table Student( " +
						   "Email varchar(" + maxLengthEmail + ") unique not null, " +
						   "AlternativeEmail varchar(" + maxLengthEmail + ") unique, " +
						   "foreign key (Email) references Contact (Email), " +
						   "primary key (Email) " +
						       "on conflict abort, " +
						   "check (AlternativeEmail <> Email));";
	        
			executeCreation(query);
		}
		
		private final void createTriggers() throws Exception {
			
			String query;
			query = "create trigger update_student_email after update of Email on Student " +
			   	    "begin " +
				    	"update RelProjectStudent set Student_Email = new.Email " +
				    		"where (Student_Email = old.Email); " +
				    "end;";
			executeCreation(query);
			
			query = "create trigger delete_student_email before delete on Student " +
					"begin " +
	    	   			"delete from RelProjectStudent " +
	    	   				"where (Student_Email = old.Email); " +
	    	   		"end;";
			executeCreation(query);
		}
		
		public final boolean add(String email, String name, String fname, String phoneNr, 
				String moreInfo, String altEmail) throws Exception {
			
			checkConsistance(email, name, fname, phoneNr, moreInfo, altEmail);
			
			boolean success = super.add(email, name, fname, phoneNr, moreInfo);
			
			Vector<Object> v = new Vector<Object>(2);
			v.add(0, email);
			v.add(1, altEmail);
			
			return (success && super.add("Student", v));
		}
		
		public final boolean updateAlternativeEmail(String email, String newAltEmail) throws Exception {
			
			checkEmail(email);
			checkAlternativeEmail(email, newAltEmail);
			
			return super.update("Student", "Email", email, "AlternativeEmail", newAltEmail);
		}
		
		public Student getStudent(String email) throws Exception {
			// Returns the requested object or null if nothing was found
			
			Statement stat = Main.connection.createStatement();
			ResultSet rs = stat.executeQuery("select * from Student natural join Contact " +
											 "where (Student.Email = '" + email + "');");
			if (rs.next()) {
				Student s = new Student(rs.getString("Email"), rs.getString("Name"),
						rs.getString("Forename"), rs.getString("PhoneNumber"), 
						rs.getString("MoreInformation"), rs.getString("AlternativeEmail"));
				rs.close();
				return s;
			}
			rs.close();
			return null;
		}
		
		public Vector<Student> getAllStudents() throws Exception {
			
			Statement stat = Main.connection.createStatement();
			ResultSet rs = stat.executeQuery("select * from Student natural join Contact;");
			
			Vector<Student> v = new Vector<Student>();
			int counter = 0;
			while (rs.next()) {
				v.add(counter, new Student(rs.getString("Email"), rs.getString("Name"),
					rs.getString("Forename"), rs.getString("PhoneNumber"), 
					rs.getString("MoreInformation"), rs.getString("AlternativeEmail")));
				counter++;
			}
			rs.close();
			return v;
		}
		
		private final void checkConsistance(String email, String name, String fname, String phoneNr, 
				String moreInfo, String altEmail) throws Exception {
			
			super.checkConsistance(email, name, fname, phoneNr, moreInfo);
			checkAlternativeEmail(email, altEmail);
		}
		
		private final void checkAlternativeEmail(String email, String altEmail) throws Exception {
			
			if (altEmail != null) {
				if (email.toLowerCase().equals(altEmail.toLowerCase())) {
					throw (new Exception("Alternative email can't be the same as the original email!"));
				}
				if (altEmail.length() < minLengthEmail) {
					throw (new Exception("Alternative email has to be at least " + 
							minLengthEmail + " characters long!"));
				}
				if (altEmail.length() > maxLengthEmail) {
					throw (new Exception("Alternative email exceeds maximum length (" +
						maxLengthEmail + " characters)!"));
				}
			}
		}
		
	}
	
}
